﻿using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;

namespace WikiUploader.Classes
{
   /// <summary>
   /// Settings for the wiki uploader.
   /// </summary>
   public class Settings
   {
      private readonly MainViewModel m_MainViewModel;
      private const string DATA_PATH = "WikiUploaderData.dat";

      /// <summary>
      /// Initializes a new settings object.
      /// </summary>
      /// <param name="mainViewModel">Main view model.</param>
      public Settings (MainViewModel mainViewModel)
      {
         this.m_MainViewModel = mainViewModel;

         if (!File.Exists (DATA_PATH))
            this.CreateDatabase ();
         else
            this.Load ();
      }

      /// <summary>
      /// Gets the sqlite connection.
      /// </summary>
      public SQLiteConnection Connection { get; private set; }

      /// <summary>
      /// Gets a connection string to a sqlite database.
      /// </summary>
      private string ConnString
      {
         get { return string.Format ("Data Source={0};Version=3;", DATA_PATH); }
      }

      /// <summary>
      /// Connects to the database.
      /// </summary>
      private void Connect ()
      {
         if (this.Connection == null)
         {
            this.Connection = new SQLiteConnection (this.ConnString);
            this.Connection.Open ();
         }
      }

      /// <summary>
      /// Loads the settings.
      /// </summary>
      public void Load ()
      {
         this.Connect ();
         var command = this.Connection.CreateCommand ();
         command.CommandText = "SELECT * FROM lastuse;";
         using (var reader = command.ExecuteReader ())
         {
            reader.Read ();
            var siteIndex = reader.GetOrdinal ("site");
            var userIndex = reader.GetOrdinal ("user");
            this.m_MainViewModel.WikiSite = reader.GetString (siteIndex);
            this.m_MainViewModel.UserName = reader.GetString (userIndex);
         }
         command.CommandText = string.Format("SELECT password FROM users WHERE user='{0}';", this.m_MainViewModel.UserName);
         this.m_MainViewModel.Password = command.ExecuteScalar ().ToString ();
      }

      /// <summary>
      /// Saves the settings.
      /// </summary>
      public void Save ()
      {
         bool save = this.GetSites ().All (site => this.m_MainViewModel.WikiSite != site);
         if (save)
            this.SaveSite ();
         save = this.GetUsers ().All (user => this.m_MainViewModel.UserName != user);
         if (save)
            this.SaveUser ();

         this.SaveLastUse ();
      }

      /// <summary>
      /// Gets an enumerable list of sites.
      /// </summary>
      /// <returns>List of sites in database.</returns>
      private IEnumerable <string> GetSites ()
      {
         var command = this.Connection.CreateCommand ();
         var sites = new List <string> ();
         command.CommandText = "SELECT site FROM sites;";
         using (var reader = command.ExecuteReader ())
         {
            while (reader.Read ())
               sites.Add (reader.GetString (0));
         }
         return sites;
      }

      /// <summary>
      /// Saves the current wiki site to the database.
      /// </summary>
      private void SaveSite ()
      {
         var command = this.Connection.CreateCommand ();
         command.CommandText = string.Format ("INSERT INTO sites ('site') VALUES ('{0}');",
            this.m_MainViewModel.WikiSite);
         command.ExecuteNonQuery ();
      }

      /// <summary>
      /// Gets an enumerable list of sites.
      /// </summary>
      /// <returns>List of sites in database.</returns>
      private IEnumerable<string> GetUsers ()
      {
         var command = this.Connection.CreateCommand ();
         var users = new List<string> ();
         command.CommandText = "SELECT user FROM users;";
         using (var reader = command.ExecuteReader ())
         {
            while (reader.Read ())
               users.Add (reader.GetString (0));
         }
         return users;
      }

      /// <summary>
      /// Saves the current wiki site to the database.
      /// </summary>
      private void SaveUser ()
      {
         var command = this.Connection.CreateCommand ();
         command.CommandText = string.Format ("INSERT INTO users ('user', 'password') VALUES ('{0}', '{1}');",
            this.m_MainViewModel.UserName, this.m_MainViewModel.Password);
         command.ExecuteNonQuery ();
      }

      /// <summary>
      /// Saves the last used items.
      /// </summary>
      private void SaveLastUse ()
      {
         var command = this.Connection.CreateCommand ();
         command.CommandText = "DROP TABLE lastuse;";
         command.ExecuteNonQuery ();
         command.CommandText = "CREATE TABLE lastuse (site TEXT, user TEXT);";
         command.ExecuteNonQuery ();
         command.CommandText = string.Format ("INSERT INTO lastuse ('site', 'user') VALUES ('{0}', '{1}');",
                                              this.m_MainViewModel.WikiSite, this.m_MainViewModel.UserName);
         command.ExecuteNonQuery ();
      }

      /// <summary>
      /// Creates the setting database.
      /// </summary>
      private void CreateDatabase ()
      {
         this.Connect ();
         var command = this.Connection.CreateCommand ();
         command.CommandText = "CREATE TABLE sites (site TEXT);";
         command.ExecuteNonQuery ();
         command.CommandText = "CREATE TABLE users (user TEXT, password TEXT);";
         command.ExecuteNonQuery ();
         command.CommandText = "CREATE TABLE lastuse (site TEXT, user TEXT);";
         command.ExecuteNonQuery ();
      }
   }
}
